/** <a href="http://www.cpupk.com/decompiler">Eclipse Class Decompiler</a> plugin, Copyright (c) 2017 Chen Chao. **/
package com.minierp.dao.hibernate;

import com.minierp.bean.UserBean;
import com.minierp.dao.EstablishmentDao;
import com.minierp.dao.hibernate.BaseDaoImpl;
import com.minierp.helper.EncCommonHelper;
import com.minierp.helper.PageHelper;
import com.minierp.model.Establishment;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

public class EstablishmentDaoImpl extends BaseDaoImpl
		implements
			EstablishmentDao {
	public List<?> getList(String estType, long typeId, long levelId,
			long areaId, String searchKey, long managerId, boolean checkTs,
			long tsId, UserBean ub, boolean justEnabled, String orderType,
			int startIndex, int num) throws Exception {
		String str = "";
		String tblStr = " com.minierp.model.Establishment e";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (areaId != 0L) {
			tblStr = tblStr + ",com.minierp.model.Area a";
			str = str + " and e.areaId=a.id and (a.id=" + areaId
					+ " or (a.parentIdStr like \'%;" + areaId + ";%\'))";
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = str + " and (" + "(e.estName like :estName) "
					+ " or (e.estCode like :estCode)"
					+ " or (e.nameSpell like :nameSpell)"
					+ " or (e.linkman like :linkman)" + ")";
		}

		if (justEnabled) {
			str = str + " and e.enabled=\'Y\'";
		}

		if (typeId != 0L) {
			str = str + " and e.estTypeId=" + typeId;
		}

		if (levelId != 0L) {
			str = str + " and e.estLevelId=" + levelId;
		}

		if (managerId != 0L) {
			str = str + " and e.managerId=" + managerId;
		}

		if (checkTs) {
			if (tsId != 0L) {
				str = str + " and e.tallySetId=" + tsId;
			} else {
				str = str
						+ EncCommonHelper.createTallySetSQLStr("e.tallySetId",
								ub, true);
			}
		}

		String orderStr = " order by e.nameSpell";
		if (orderType != null && orderType.equals("BUS_TIME")) {
			orderStr = " order by e.busTime desc,e.nameSpell";
		}

		String sql = "select e from " + tblStr + " where e.estType=:estType "
				+ " and e.status=\'Y\' " + str + orderStr;
		Session sess = null;

		List arg26;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			q.setString("estType", estType);
			if (searchKey != null && !searchKey.equals("")) {
				String nameSpell = searchKey.toUpperCase();
				q.setString("estName", "%" + searchKey + "%");
				q.setString("estCode", "%" + searchKey + "%");
				q.setString("nameSpell", "%" + nameSpell + "%");
				q.setString("linkman", "%" + searchKey + "%");
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg26 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg26;
	}

	public int getListCount(String estType, long typeId, long levelId,
			long areaId, String searchKey, long managerId, boolean checkTs,
			long tsId, UserBean ub, boolean justEnabled) throws Exception {
		String str = "";
		String tblStr = " com.minierp.model.Establishment e";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (areaId != 0L) {
			tblStr = tblStr + ",com.minierp.model.Area a";
			str = str + " and e.areaId=a.id and (a.id=" + areaId
					+ " or (a.parentIdStr like \'%;" + areaId + ";%\'))";
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = str + " and (" + "(e.estName like :estName) "
					+ " or (e.estCode like :estCode)"
					+ " or (e.nameSpell like :nameSpell)"
					+ " or (e.linkman like :linkman)" + ")";
		}

		if (justEnabled) {
			str = str + " and e.enabled=\'Y\'";
		}

		if (typeId != 0L) {
			str = str + " and e.estTypeId=" + typeId;
		}

		if (levelId != 0L) {
			str = str + " and e.estLevelId=" + levelId;
		}

		if (managerId != 0L) {
			str = str + " and e.managerId=" + managerId;
		}

		if (checkTs) {
			if (tsId != 0L) {
				str = str + " and e.tallySetId=" + tsId;
			} else {
				str = str
						+ EncCommonHelper.createTallySetSQLStr("e.tallySetId",
								ub, true);
			}
		}

		String sql = "select count(*) from " + tblStr
				+ " where e.estType=:estType " + " and e.status=\'Y\' " + str;
		Session sess = null;

		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			q.setString("estType", estType);
			if (searchKey != null && !searchKey.equals("")) {
				String list = searchKey.toUpperCase();
				q.setString("estName", "%" + searchKey + "%");
				q.setString("estCode", "%" + searchKey + "%");
				q.setString("nameSpell", "%" + list + "%");
				q.setString("linkman", "%" + searchKey + "%");
			}

			List list1 = q.list();
			if (!list1.isEmpty()) {
				int arg22 = Integer.parseInt(list1.get(0).toString());
				return arg22;
			}
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return 0;
	}

	public List<?> getList(String estType, String searchKey,
			String managerName, String phone, String address, String mark,
			long typeId, long levelId, long areaId, Date startDate,
			Date endDate, Date busiStart, Date busiEnd, Date commStart,
			Date commEnd, boolean checkTs, long tsId, UserBean ub,
			boolean justEnabled, int startIndex, int num) throws Exception {
		String str = "";
		String tblStr = " com.minierp.model.Establishment e";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (areaId != 0L) {
			tblStr = tblStr + ",com.minierp.model.Area a";
			str = str + " and e.areaId=a.id and (a.id=" + areaId
					+ " or (a.parentIdStr like \'%;" + areaId + ";%\'))";
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = str + " and (" + "(e.estName like :estName) "
					+ " or (e.estCode like :estCode)"
					+ " or (e.nameSpell like :nameSpell)" + ")";
		}

		if (managerName != null) {
			managerName = managerName.trim();
		}

		if (managerName != null && !managerName.equals("")) {
			str = str + " and (e.managerName like :managerName)";
		}

		if (phone != null) {
			phone = phone.trim();
		}

		if (phone != null && !phone.equals("")) {
			str = str + " and (e.phone like :phone)";
		}

		if (address != null) {
			address = address.trim();
		}

		if (address != null && !address.equals("")) {
			str = str + " and (e.address like :address)";
		}

		if (mark != null) {
			mark = mark.trim();
		}

		if (mark != null && !mark.equals("")) {
			str = str + " and (e.mark like :mark)";
		}

		if (typeId != 0L) {
			str = str + " and e.estTypeId=" + typeId;
		}

		if (levelId != 0L) {
			str = str + " and e.estLevelId=" + levelId;
		}

		if (startDate != null) {
			str = str + " and e.createTime>:startDate";
		}

		if (endDate != null) {
			str = str + " and e.createTime<:endDate";
		}

		if (busiStart != null) {
			str = str + " and e.busTime>:busiStart";
		}

		if (busiEnd != null) {
			str = str + " and e.busTime<:busiEnd";
		}

		if (commStart != null) {
			str = str + " and e.commTime>:commStart";
		}

		if (commEnd != null) {
			str = str + " and e.commTime<:commEnd";
		}

		if (justEnabled) {
			str = str + " and e.enabled=\'Y\'";
		}

		if (checkTs) {
			if (tsId != 0L) {
				str = str + " and e.tallySetId=" + tsId;
			} else {
				str = str
						+ EncCommonHelper.createTallySetSQLStr("e.tallySetId",
								ub, true);
			}
		}

		String sql = "select e from " + tblStr + " where e.estType=:estType "
				+ str + " order by e.status desc,e.nameSpell";
		Session sess = null;

		List arg32;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			q.setString("estType", estType);
			if (searchKey != null && !searchKey.equals("")) {
				String nameSpell = searchKey.toUpperCase();
				q.setString("estName", "%" + searchKey + "%");
				q.setString("estCode", "%" + searchKey + "%");
				q.setString("nameSpell", "%" + nameSpell + "%");
			}

			if (managerName != null && !managerName.equals("")) {
				q.setString("managerName", "%" + managerName + "%");
			}

			if (phone != null && !phone.equals("")) {
				q.setString("phone", "%" + phone + "%");
			}

			if (address != null && !address.equals("")) {
				q.setString("address", "%" + address + "%");
			}

			if (mark != null && !mark.equals("")) {
				q.setString("mark", "%" + mark + "%");
			}

			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (busiStart != null) {
				q.setDate("busiStart", busiStart);
			}

			if (busiEnd != null) {
				q.setDate("busiEnd", busiEnd);
			}

			if (commStart != null) {
				q.setDate("commStart", commStart);
			}

			if (commEnd != null) {
				q.setDate("commEnd", commEnd);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg32 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg32;
	}

	public int getListCount(String estType, String searchKey,
			String managerName, String phone, String address, String mark,
			long typeId, long levelId, long areaId, Date startDate,
			Date endDate, Date busiStart, Date busiEnd, Date commStart,
			Date commEnd, boolean checkTs, long tsId, UserBean ub,
			boolean justEnabled) throws Exception {
		String str = "";
		String tblStr = " com.minierp.model.Establishment e";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (areaId != 0L) {
			tblStr = tblStr + ",com.minierp.model.Area a";
			str = str + " and e.areaId=a.id and (a.id=" + areaId
					+ " or (a.parentIdStr like \'%;" + areaId + ";%\'))";
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = str + " and (" + "(e.estName like :estName) "
					+ " or (e.estCode like :estCode)"
					+ " or (e.nameSpell like :nameSpell)" + ")";
		}

		if (managerName != null) {
			managerName = managerName.trim();
		}

		if (managerName != null && !managerName.equals("")) {
			str = str + " and (e.managerName like :managerName)";
		}

		if (phone != null) {
			phone = phone.trim();
		}

		if (phone != null && !phone.equals("")) {
			str = str + " and (e.phone like :phone)";
		}

		if (address != null) {
			address = address.trim();
		}

		if (address != null && !address.equals("")) {
			str = str + " and (e.address like :address)";
		}

		if (mark != null) {
			mark = mark.trim();
		}

		if (mark != null && !mark.equals("")) {
			str = str + " and (e.mark like :mark)";
		}

		if (typeId != 0L) {
			str = str + " and e.estTypeId=" + typeId;
		}

		if (levelId != 0L) {
			str = str + " and e.estLevelId=" + levelId;
		}

		if (startDate != null) {
			str = str + " and e.createTime>:startDate";
		}

		if (endDate != null) {
			str = str + " and e.createTime<:endDate";
		}

		if (busiStart != null) {
			str = str + " and e.busTime>:busiStart";
		}

		if (busiEnd != null) {
			str = str + " and e.busTime<:busiEnd";
		}

		if (commStart != null) {
			str = str + " and e.commTime>:commStart";
		}

		if (commEnd != null) {
			str = str + " and e.commTime<:commEnd";
		}

		if (justEnabled) {
			str = str + " and e.enabled=\'Y\'";
		}

		if (checkTs) {
			if (tsId != 0L) {
				str = str + " and e.tallySetId=" + tsId;
			} else {
				str = str
						+ EncCommonHelper.createTallySetSQLStr("e.tallySetId",
								ub, true);
			}
		}

		String sql = "select count(e.id) from " + tblStr
				+ " where e.estType=:estType " + str;
		Session sess = null;

		int arg30;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			q.setString("estType", estType);
			if (searchKey != null && !searchKey.equals("")) {
				String list = searchKey.toUpperCase();
				q.setString("estName", "%" + searchKey + "%");
				q.setString("estCode", "%" + searchKey + "%");
				q.setString("nameSpell", "%" + list + "%");
			}

			if (managerName != null && !managerName.equals("")) {
				q.setString("managerName", "%" + managerName + "%");
			}

			if (phone != null && !phone.equals("")) {
				q.setString("phone", "%" + phone + "%");
			}

			if (address != null && !address.equals("")) {
				q.setString("address", "%" + address + "%");
			}

			if (mark != null && !mark.equals("")) {
				q.setString("mark", "%" + mark + "%");
			}

			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (busiStart != null) {
				q.setDate("busiStart", busiStart);
			}

			if (busiEnd != null) {
				q.setDate("busiEnd", busiEnd);
			}

			if (commStart != null) {
				q.setDate("commStart", commStart);
			}

			if (commEnd != null) {
				q.setDate("commEnd", commEnd);
			}

			List list1 = q.list();
			if (list1.isEmpty()) {
				return 0;
			}

			arg30 = Integer.parseInt(list1.get(0).toString());
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg30;
	}

	public List<?> getManageList(String estType, long stafferId,
			String searchKey, String managerName, String phone, String address,
			long typeId, long levelId, long areaId, Date startDate,
			Date endDate, Date busiStart, Date busiEnd, Date commStart,
			Date commEnd, boolean checkTs, long tsId, UserBean ub,
			String orderBy, int startIndex, int num) throws Exception {
		String orderStr = "";
		if (orderBy != null && !orderBy.equals("")) {
			if (orderBy.equals("BUS_TIME")) {
				orderStr = " order by e.status desc,e.busTime desc";
			} else if (orderBy.equals("COMM_TIME")) {
				orderStr = " order by e.status desc,e.commTime desc";
			} else if (orderBy.equals("EST_NAME")) {
				orderStr = " order by e.status desc,e.nameSpell";
			} else if (orderBy.equals("BUS_COST")) {
				orderStr = " order by e.status desc,e.busCost desc";
			}
		} else {
			orderStr = " order by e.status desc,e.commTime desc,e.nameSpell";
		}

		String tblStr = " com.minierp.model.Establishment e,com.minierp.model.Staffer s";
		String str = " and e.managerId=s.id and (s.id=" + stafferId
				+ " or (s.superiorIdStr like \'%;" + stafferId + ";%\'))";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (areaId != 0L) {
			tblStr = tblStr + ",com.minierp.model.Area a";
			str = str + " and e.areaId=a.id and (a.id=" + areaId
					+ " or (a.parentIdStr like \'%;" + areaId + ";%\'))";
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = str + " and (" + "(e.estName like :estName) "
					+ " or (e.estCode like :estCode)"
					+ " or (e.nameSpell like :nameSpell)" + ")";
		}

		if (managerName != null) {
			managerName = managerName.trim();
		}

		if (managerName != null && !managerName.equals("")) {
			str = str + " and (e.managerName like :managerName)";
		}

		if (phone != null) {
			phone = phone.trim();
		}

		if (phone != null && !phone.equals("")) {
			str = str + " and (e.phone like :phone)";
		}

		if (address != null) {
			address = address.trim();
		}

		if (address != null && !address.equals("")) {
			str = str + " and (e.address like :address)";
		}

		if (typeId != 0L) {
			str = str + " and e.estTypeId=" + typeId;
		}

		if (levelId != 0L) {
			str = str + " and e.estLevelId=" + levelId;
		}

		if (startDate != null) {
			str = str + " and e.createTime>:startDate";
		}

		if (endDate != null) {
			str = str + " and e.createTime<:endDate";
		}

		if (busiStart != null) {
			str = str + " and e.busTime>:busiStart";
		}

		if (busiEnd != null) {
			str = str + " and e.busTime<:busiEnd";
		}

		if (commStart != null) {
			str = str + " and e.commTime>:commStart";
		}

		if (commEnd != null) {
			str = str + " and e.commTime<:commEnd";
		}

		if (checkTs) {
			if (tsId != 0L) {
				str = str + " and e.tallySetId=" + tsId;
			} else {
				str = str
						+ EncCommonHelper.createTallySetSQLStr("e.tallySetId",
								ub, true);
			}
		}

		String sql = "select e from " + tblStr + " where e.enabled=\'Y\'"
				+ " and e.estType=:estType" + str + orderStr;
		Session sess = null;

		List arg34;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			q.setString("estType", estType);
			if (searchKey != null && !searchKey.equals("")) {
				String nameSpell = searchKey.toUpperCase();
				q.setString("estName", "%" + searchKey + "%");
				q.setString("estCode", "%" + searchKey + "%");
				q.setString("nameSpell", "%" + nameSpell + "%");
			}

			if (managerName != null && !managerName.equals("")) {
				q.setString("managerName", "%" + managerName + "%");
			}

			if (phone != null && !phone.equals("")) {
				q.setString("phone", "%" + phone + "%");
			}

			if (address != null && !address.equals("")) {
				q.setString("address", "%" + address + "%");
			}

			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (busiStart != null) {
				q.setDate("busiStart", busiStart);
			}

			if (busiEnd != null) {
				q.setDate("busiEnd", busiEnd);
			}

			if (commStart != null) {
				q.setDate("commStart", commStart);
			}

			if (commEnd != null) {
				q.setDate("commEnd", commEnd);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg34 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg34;
	}

	public int getManageListCount(String estType, long stafferId,
			String searchKey, String managerName, String phone, String address,
			long typeId, long levelId, long areaId, Date startDate,
			Date endDate, Date busiStart, Date busiEnd, Date commStart,
			Date commEnd, boolean checkTs, long tsId, UserBean ub)
			throws Exception {
		String tblStr = " com.minierp.model.Establishment e,com.minierp.model.Staffer s";
		String str = " and e.managerId=s.id and (s.id=" + stafferId
				+ " or (s.superiorIdStr like \'%;" + stafferId + ";%\'))";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (areaId != 0L) {
			tblStr = tblStr + ",com.minierp.model.Area a";
			str = str + " and e.areaId=a.id and (a.id=" + areaId
					+ " or (a.parentIdStr like \'%;" + areaId + ";%\'))";
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = str + " and (" + "(e.estName like :estName) "
					+ " or (e.estCode like :estCode)"
					+ " or (e.nameSpell like :nameSpell)" + ")";
		}

		if (managerName != null) {
			managerName = managerName.trim();
		}

		if (managerName != null && !managerName.equals("")) {
			str = str + " and (e.managerName like :managerName)";
		}

		if (phone != null) {
			phone = phone.trim();
		}

		if (phone != null && !phone.equals("")) {
			str = str + " and (e.phone like :phone)";
		}

		if (address != null) {
			address = address.trim();
		}

		if (address != null && !address.equals("")) {
			str = str + " and (e.address like :address)";
		}

		if (typeId != 0L) {
			str = str + " and e.estTypeId=" + typeId;
		}

		if (levelId != 0L) {
			str = str + " and e.estLevelId=" + levelId;
		}

		if (startDate != null) {
			str = str + " and e.createTime>:startDate";
		}

		if (endDate != null) {
			str = str + " and e.createTime<:endDate";
		}

		if (busiStart != null) {
			str = str + " and e.busTime>:busiStart";
		}

		if (busiEnd != null) {
			str = str + " and e.busTime<:busiEnd";
		}

		if (commStart != null) {
			str = str + " and e.commTime>:commStart";
		}

		if (commEnd != null) {
			str = str + " and e.commTime<:commEnd";
		}

		if (checkTs) {
			if (tsId != 0L) {
				str = str + " and e.tallySetId=" + tsId;
			} else {
				str = str
						+ EncCommonHelper.createTallySetSQLStr("e.tallySetId",
								ub, true);
			}
		}

		String sql = "select count(e.id) from " + tblStr
				+ " where e.enabled=\'Y\'" + " and e.estType=:estType" + str;
		Session sess = null;

		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			q.setString("estType", estType);
			if (searchKey != null && !searchKey.equals("")) {
				String list = searchKey.toUpperCase();
				q.setString("estName", "%" + searchKey + "%");
				q.setString("estCode", "%" + searchKey + "%");
				q.setString("nameSpell", "%" + list + "%");
			}

			if (managerName != null && !managerName.equals("")) {
				q.setString("managerName", "%" + managerName + "%");
			}

			if (phone != null && !phone.equals("")) {
				q.setString("phone", "%" + phone + "%");
			}

			if (address != null && !address.equals("")) {
				q.setString("address", "%" + address + "%");
			}

			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (busiStart != null) {
				q.setDate("busiStart", busiStart);
			}

			if (busiEnd != null) {
				q.setDate("busiEnd", busiEnd);
			}

			if (commStart != null) {
				q.setDate("commStart", commStart);
			}

			if (commEnd != null) {
				q.setDate("commEnd", commEnd);
			}

			List list1 = q.list();
			if (!list1.isEmpty()) {
				int arg30 = Integer.parseInt(list1.get(0).toString());
				return arg30;
			}
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return 0;
	}

	public int delete(final long id) {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "delete com.minierp.model.Establishment where id=:id";
						return new Integer(session.createQuery(hql)
								.setLong("id", id).executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public Establishment getByEstCode(String estCode, String estType) {
		String sql = "from com.minierp.model.Establishment as obj where obj.estCode = :estCode and obj.estType=:estType order by obj.nameSpell";
		String[] paramNames = new String[]{"estCode", "estType"};
		Object[] values = new Object[]{estCode, estType};
		List list = this.getHibernateTemplate().findByNamedParam(sql,
				paramNames, values);
		return list.isEmpty() ? null : (Establishment) list.get(0);
	}

	public Establishment getByEstName(String estName, String estType) {
		String sql = "from com.minierp.model.Establishment as obj where obj.estName = :estName and obj.estType=:estType order by obj.nameSpell";
		String[] paramNames = new String[]{"estName", "estType"};
		Object[] values = new Object[]{estName, estType};
		List list = this.getHibernateTemplate().findByNamedParam(sql,
				paramNames, values);
		return list.isEmpty() ? null : (Establishment) list.get(0);
	}

	public Establishment getEnabledByEstName(String estName, String estType) {
		String sql = "from com.minierp.model.Establishment as obj where obj.estName = :estName and obj.estType=:estType and obj.enabled=\'Y\' order by obj.nameSpell";
		String[] paramNames = new String[]{"estName", "estType"};
		Object[] values = new Object[]{estName, estType};
		List list = this.getHibernateTemplate().findByNamedParam(sql,
				paramNames, values);
		return list.isEmpty() ? null : (Establishment) list.get(0);
	}

	public List<?> getList(String estName, String estType, long exceptId) {
		String sql = "from com.minierp.model.Establishment as obj where (obj.estName like :estName) and obj.estType=:estType";
		if (exceptId != 0L) {
			sql = sql + " and obj.id!=" + exceptId;
		}

		sql = sql + " order by obj.nameSpell";
		String[] paramNames = new String[]{"estName", "estType"};
		Object[] values = new Object[]{"%" + estName + "%", estType};
		return this.getHibernateTemplate().findByNamedParam(sql, paramNames,
				values);
	}

	public List<?> getAllChildren(long superiorId) {
		String sql = "from com.minierp.model.Establishment as obj where obj.superiorIdStr like :superiorIdStr order by obj.nameSpell";
		String superiorIdStr = "%;" + superiorId + ";%";
		String[] paramNames = new String[]{"superiorIdStr"};
		Object[] values = new Object[]{superiorIdStr};
		return this.getHibernateTemplate().findByNamedParam(sql, paramNames,
				values);
	}

	public int update(final long fromManagerId, final long toManagerId,
			final String toManagerName, final String estType) {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "update com.minierp.model.Establishment set managerId="
								+ toManagerId
								+ ",managerName=:managerName where estType=:estType and managerId="
								+ fromManagerId;
						return new Integer(session.createQuery(hql)
								.setString("managerName", toManagerName)
								.setString("estType", estType).executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public int update(long[] ids, final long toManagerId,
			final String toManagerName, final String estType) {
		if (ids != null && ids.length != 0) {
			final String idStr = PageHelper.createIdArrStr(ids, "id", false);
			Object obj = this.getHibernateTemplate().execute(
					new HibernateCallback() {
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							String hql = "update com.minierp.model.Establishment set managerId="
									+ toManagerId
									+ ",managerName=:managerName where estType=:estType and "
									+ idStr;
							return new Integer(session.createQuery(hql)
									.setString("managerName", toManagerName)
									.setString("estType", estType)
									.executeUpdate());
						}
					});
			return ((Integer) obj).intValue();
		} else {
			return 0;
		}
	}

	public int update(final long id, final Date lastSmsTime) {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "update com.minierp.model.Establishment set smsTime=:smsTime where id="
								+ id;
						return new Integer(session.createQuery(hql)
								.setTimestamp("smsTime", lastSmsTime)
								.executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public int update(long[] ids, final long tsId) {
		if (ids != null && ids.length != 0) {
			final String idStr = PageHelper.createIdArrStr(ids, "id", false);
			Object obj = this.getHibernateTemplate().execute(
					new HibernateCallback() {
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							String hql = "update com.minierp.model.Establishment set tallySetId="
									+ tsId + " where " + idStr;
							return new Integer(session.createQuery(hql)
									.executeUpdate());
						}
					});
			return ((Integer) obj).intValue();
		} else {
			return 0;
		}
	}

	public Object[] getStatusValues(long id) {
		String sql = "select e.estName,e.phone,e.email,e.address from com.minierp.model.Establishment e where e.id="
				+ id;
		List list = this.getHibernateTemplate().find(sql);
		return list.size() > 0 ? (Object[]) list.get(0) : null;
	}

	public List getLogList(long estId) {
		String sql = "from com.minierp.model.EstablishmentLog l where l.estId="
				+ estId + " order by id desc";
		return this.getHibernateTemplate().find(sql);
	}

	public int logCorporate(final long fromClientId, final long toClientId) {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "update com.minierp.model.EstablishmentLog set estId="
								+ toClientId + " where estId=" + fromClientId;
						Query q = session.createQuery(hql);
						return new Integer(q.executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public List<?> getAppList(String estType, String estName, String phon,
			String addr, boolean checkTs, UserBean ub, String orde,
			int startIndex, int num) throws Exception {
		String str = " and e.managerId=s.id and (s.id="
				+ ub.getStaffer().getId() + " or (s.superiorIdStr like \'%;"
				+ ub.getStaffer().getId() + ";%\'))";
		if (estName != null && !estName.equals("")) {
			str = str + " and (e.estName like :estName)";
		}

		if (phon != null && !phon.equals("")) {
			str = str + " and (e.phone like :phon)";
		}

		if (addr != null && !addr.equals("")) {
			str = str + " and (e.address like :addr)";
		}

		if (checkTs) {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("e.tallySetId", ub,
							true);
		}

		String ordeStr = " order by e.status desc,e.commTime desc,e.nameSpell";
		if (orde != null) {
			if (orde.equals("EST_NAME")) {
				ordeStr = " order by e.nameSpell";
			} else if (orde.equals("BUSI_TIME")) {
				ordeStr = " order by e.busTime desc,e.nameSpell";
			} else if (orde.equals("COMM_TIME")) {
				ordeStr = " order by e.commTime desc,e.nameSpell";
			} else if (orde.equals("BUSI_COST")) {
				ordeStr = " order by e.busCost desc,e.nameSpell";
			}
		}

		String sql = "select e from  com.minierp.model.Establishment e,com.minierp.model.Staffer s where e.enabled=\'Y\' and e.estType=:estType"
				+ str + ordeStr;
		Session sess = null;

		List arg15;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			q.setString("estType", estType);
			if (estName != null && !estName.equals("")) {
				q.setString("estName", "%" + estName + "%");
			}

			if (phon != null && !phon.equals("")) {
				q.setString("phon", "%" + phon + "%");
			}

			if (addr != null && !addr.equals("")) {
				q.setString("addr", "%" + addr + "%");
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg15 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg15;
	}

	public int getAppListCount(String estType, String estName, String phon,
			String addr, boolean checkTs, UserBean ub) throws Exception {
		String str = " and e.managerId=s.id and (s.id="
				+ ub.getStaffer().getId() + " or (s.superiorIdStr like \'%;"
				+ ub.getStaffer().getId() + ";%\'))";
		if (estName != null && !estName.equals("")) {
			str = str + " and (e.estName like :estName)";
		}

		if (phon != null && !phon.equals("")) {
			str = str + " and (e.phone like :phon)";
		}

		if (addr != null && !addr.equals("")) {
			str = str + " and (e.address like :addr)";
		}

		if (checkTs) {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("e.tallySetId", ub,
							true);
		}

		String sql = "select count(*) from  com.minierp.model.Establishment e,com.minierp.model.Staffer s where e.enabled=\'Y\' and e.estType=:estType"
				+ str;
		Session sess = null;

		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			q.setString("estType", estType);
			if (estName != null && !estName.equals("")) {
				q.setString("estName", "%" + estName + "%");
			}

			if (phon != null && !phon.equals("")) {
				q.setString("phon", "%" + phon + "%");
			}

			if (addr != null && !addr.equals("")) {
				q.setString("addr", "%" + addr + "%");
			}

			List list = q.list();
			if (!list.isEmpty()) {
				int arg12 = Integer.parseInt(list.get(0).toString());
				return arg12;
			}
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return 0;
	}
}